package pe.edu.upc.dsd.mercadolibre;

import java.util.ArrayList;
import java.util.Collection;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class ArticuloDAO extends BaseDAO {

	public Collection<Articulo> ListaArticulosDB() throws DAOExcepcion {
		System.out.println("Listar: Buscar Artiuculos()");
		Collection<Articulo> listaArticulos = new ArrayList<Articulo>();
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			String query = "select * from producto where idEstado=1";
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			rs = stmt.executeQuery();
			while (rs.next()) {
				Articulo objInstr = new Articulo();
				objInstr.setId(rs.getInt("idproducto"));
				objInstr.setNombre(rs.getString("nomProd"));
				objInstr.setDescripcion(rs.getString("descProd"));
				objInstr.setStock(rs.getInt("stockProd"));
				objInstr.setPrecio(rs.getDouble("precProd"));
				objInstr.setImagen(rs.getString("imagProd"));
				objInstr.setVendedor(rs.getString("idVendedor"));
				;

				listaArticulos.add(objInstr);
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return listaArticulos;
	}

	public Collection<Articulo> BuscarArticulosDB(String TxtB)
			throws DAOExcepcion {
		System.out.println("Listar: Buscar Artiuculos()");
		Collection<Articulo> listaArticulos = new ArrayList<Articulo>();
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			String query = "select * from producto where nomProd like ? and idEstado=1";
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setString(1, "%" + TxtB + "%");
			rs = stmt.executeQuery();

			while (rs.next()) {
				Articulo objInstr = new Articulo();
				objInstr.setId(rs.getInt("idproducto"));
				objInstr.setNombre(rs.getString("nomProd"));
				objInstr.setDescripcion(rs.getString("descProd"));
				objInstr.setStock(rs.getInt("stockProd"));
				objInstr.setPrecio(rs.getDouble("precProd"));
				objInstr.setImagen(rs.getString("imagProd"));
				objInstr.setVendedor(rs.getString("idVendedor"));
				;

				listaArticulos.add(objInstr);
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return listaArticulos;
	}

	public Articulo ObtenerArticuloDB(int id) throws DAOExcepcion {
		System.out.println("Listar: Buscar Artiuculos()");
		Articulo ArticuloB = new Articulo();
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			String query = "select * from producto where idproducto = ?";
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setInt(1, id);
			rs = stmt.executeQuery();

			if (rs.next()) {
				ArticuloB.setId(rs.getInt("idproducto"));
				ArticuloB.setNombre(rs.getString("nomProd"));
				ArticuloB.setDescripcion(rs.getString("descProd"));
				ArticuloB.setStock(rs.getInt("stockProd"));
				ArticuloB.setPrecio(rs.getDouble("precProd"));
				ArticuloB.setImagen(rs.getString("imagProd"));
				ArticuloB.setVendedor(rs.getString("idVendedor"));

			}

		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}

		return ArticuloB;
	}

	public Collection<Articulo> ObtenerMisArticulos(int idUser)
			throws DAOExcepcion {
		// TODO Auto-generated method stub
		Collection<Articulo> lista = null;
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			String query = "Select * from producto where idvendedor=? and idEstado=1";
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setInt(1, idUser);
			rs = stmt.executeQuery();

			lista = new ArrayList<Articulo>();
			while (rs.next()) {
				Articulo art = new Articulo();
				art.setId(rs.getInt("idproducto"));
				art.setNombre(rs.getString("nomProd"));
				art.setDescripcion(rs.getString("descProd"));
				art.setStock(rs.getInt("stockProd"));
				art.setPrecio(rs.getDouble("precProd"));
				art.setImagen(rs.getString("imagProd"));
				art.setVendedor(rs.getString("idVendedor"));

				lista.add(art);
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return lista;
	}

	public void GuardarArticulo(Articulo vo) throws DAOExcepcion {

		String query = "INSERT INTO producto(nomProd, "
				+ "descProd, stockProd, precProd, imagProd, idVendedor) VALUES (?,?,?,?,?,?,?)";
		Connection con = null;
		PreparedStatement stmt = null;
		try {
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);

			stmt.setInt(1, vo.getId());
			stmt.setString(2, vo.getNombre());
			stmt.setString(3, vo.getDescripcion());
			stmt.setInt(4, vo.getStock());
			stmt.setString(5, String.valueOf(vo.getPrecio()));
			stmt.setString(6, vo.getImagen());
			stmt.setString(7, vo.getVendedor());

			int i = stmt.executeUpdate();
			if (i != 1) {
				throw new SQLException("No se pudo insertar");
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
	}

	public void ActualizarArticulo(Articulo vo) throws DAOExcepcion {

		String query = "update producto set nomProd=?,descProd=?,"
				+ "stockProd=?,precProd=?,imagProd=?,"
				+ "idVendedor=? where idproducto=?";
		Connection con = null;
		PreparedStatement stmt = null;
		try {
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);

			stmt.setString(1, vo.getNombre());
			stmt.setString(2, vo.getDescripcion());
			stmt.setInt(3, vo.getStock());
			stmt.setString(4, String.valueOf(vo.getPrecio()));
			stmt.setString(5, vo.getImagen());
			stmt.setString(6, vo.getVendedor());
			stmt.setInt(7, vo.getId());

			int i = stmt.executeUpdate();
			if (i != 1) {
				throw new SQLException("No se pudo actualizar");
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}

	}

	public void EliminarArticulo(Articulo vo) throws DAOExcepcion {
		String query = "update producto set idEstado=0 where idproducto=?";
		Connection con = null;
		PreparedStatement stmt = null;
		try {
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);

			stmt.setInt(1, vo.getId());

			int i = stmt.executeUpdate();
			if (i != 1) {
				throw new SQLException("No se pudo eliminar");
			}
		} catch (SQLException e) {
			// TODO: handle exception
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
	}

	public void RegistrarTransaccion(Articulo art) throws DAOExcepcion {
		// TODO Auto-generated method stub
		String query = "insert into transaccion(cantVentaTransac,estadoTransac,idCliente,idproducto)" +
				"values(?,?,?,?)";
		Connection con = null;
		PreparedStatement stmt = null;
		try {
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);

			stmt.setInt(1, art.getStock());
			stmt.setInt(2, 0);
			stmt.setString(3, art.getComprador());
			stmt.setInt(4, art.getId());

			int i = stmt.executeUpdate();
			if (i != 1) {
				throw new SQLException("No se pudo insertar");
			}
		} catch (SQLException e) {
			// TODO: handle exception
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
	}
}
